<%@Page Language="VB" %>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Managing Concurrent Updates to Individual Columns</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Managing Concurrent Updates to Individual Columns</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SELECT command: <b><span id="outSelect" runat="server"></span></b></div>
<div id="outError" runat="server">&nbsp;</div>

<b>Initial contents of the Books table:</b>
<asp:datagrid id="dgrResult1" runat="server" /><p />
<b>Contents of the Books table after editing:</b>
<asp:datagrid id="dgrResult2" runat="server" /><p />
<div id="outConcurrent" runat="server"></div><p />
<b>The Modified records in the new DataSet:</b>
<asp:datagrid id="dgrResult3" runat="server" /><p />
<div id="outUpdates" runat="server"></div><p />

<script language="vb" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.innerText = strConnect 'and display it

   'specify the SELECT statement to extract the data
   Dim strSelect As String
   strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '18610016%'"
   outSelect.innerText = strSelect   'and display it

   'create a new DataSet object
   Dim objDataSet As New DataSet()

   'create a new Connection object using the connection string
   Dim objConnect As New OleDbConnection(strConnect)

   'create a new DataAdapter using the connection object and select statement
   Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)

   Try

      'fill the dataset with data from the DataAdapter object
      objDataAdapter.Fill(objDataSet, "Books")

   Catch objError As Exception

      'display error details
      outError.innerHTML = "<b>* Error while accessing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'got the data so we're finished with the DataAdapter
   objDataAdapter = Nothing

   'accept the changes to "fix" the current state of the DataSet contents
   objDataSet.AcceptChanges()

   'declare a variable to reference the Books table
   Dim objTable As DataTable = objDataSet.Tables("Books")

   'display the contents of the Books table before changing data
   dgrResult1.DataSource = objTable.DefaultView
   dgrResult1.DataBind()  'and bind (display) the data

   '----------------------------------------------------------------
   'now modify the DataSet by changing some records in the Books table
   objTable.Rows(0)("Title") = "Amateur Double Glazing for Windows 2000"
   objTable.Rows(2)("Title") = "Amateur Topfix Hinges for Windows 2000"
   objTable.Rows(2)("PublicationDate") = "2001-09-03"
   objTable.Rows(3)("PublicationDate") = "2001-02-08"

   'display the contents of the Books table after changing the data
   dgrResult2.DataSource = objTable.DefaultView
   dgrResult2.DataBind()  'and bind (display) the data

   '-------------------------------------------------------------------
   'change some values in the original table while the DataSet is holding
   'a disconnected copy of the data to force a concurrency error

   Dim strConcurrent, strUpdate As String
   Dim intRowsAffected As Integer

   'need a new (separate) Connection and Command object
   Dim objNewConnect As New OleDbConnection(strConnect)
   Dim objNewCommand As New OleDbCommand()
   objNewCommand.Connection = objNewConnect

   Try

      objNewConnect.Open()

      'modify two of the book titles to force concurrency errors
      strConcurrent += "<b>Concurrently executed</b>:<br />"
      Dim datNow As DateTime = Now()
      Dim strNow As String = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
      strUpdate = "UPDATE BookList SET Title = 'New Book Written on " _
                & strNow & "' WHERE ISBN = '1861001622'"
      objNewCommand.CommandText = strUpdate
      intRowsAffected = objNewCommand.ExecuteNonQuery()
      strConcurrent += strUpdate & "<br /> ... <b>" _
             & CStr(intRowsAffected) & "</b> row(s) affected<br />"

      'edit the next line to force a concurrency error if none occurs
      strUpdate = "UPDATE BookList SET Title = 'Another Book Written on " _
                & strNow & "' WHERE ISBN = '1861001681'"
      objNewCommand.CommandText = strUpdate
      intRowsAffected = objNewCommand.ExecuteNonQuery()
      strConcurrent += strUpdate & "<br /> ... <b>" _
             & CStr(intRowsAffected) & "</b> row(s) affected<br />"

      objNewConnect.Close()

   Catch objError As Exception

      'display error details
      outError.InnerHtml = "<b>* Error while updating original data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   outConcurrent.InnerHtml = strConcurrent

   '----------------------------------------------------------------
   'now start the process of collecting the changed values and
   'updating the original data source

   Dim strSQL, strWhere, strRowValue, strResults, strColName As String
   Dim datRowDateValue As Date

   'create a new Command object to use for the updates
   Dim objCommand As New OleDbCommand()

   'specify the Connection object and command type for the Command
   objCommand.Connection = objConnect
   objCommand.CommandType = CommandType.Text

   'declare a variable to hold a new DataSet object
   Dim objChangeDS As DataSet

   'get *changed* records into the new DataSet
   'copies only rows with a RowState property of "Modified"
   objChangeDS = objDataSet.GetChanges(DataRowState.Modified)

   'display the modified records in the table in the new DataSet
   dgrResult3.DataSource = objChangeDS.Tables(0).DefaultView
   dgrResult3.DataBind()  'and bind (display) the data

   'open connection and apply the updates to the data source
   objConnect.Open()

   'declare a variable to hold a Transaction object
   Dim objTransaction As OleDbTransaction

   'start a transaction and assign it to the current Command object
   objTransaction = objConnect.BeginTransaction()
   objCommand.Transaction = objTransaction

   strResults = "<b>Executing Update Commands</b> ...<br />"

   'iterate through all the modified rows in the table
   Dim objRow As DataRow, objColumn As DataColumn
   For Each objRow in objChangeDS.Tables(0).Rows

      'create the two root parts of the SQL statement
      strSQL = "UPDATE BookList SET "
      strWhere = " WHERE ISBN='" _
               & objRow("ISBN", DataRowVersion.Original) & "'"

      'iterate through all the columns in this row
      For Each objColumn In objChangeDS.Tables(0).Columns
         strColName = objColumn.ColumnName

         'see if this column has been changed since the DataSet was
         'originally created by comparing Original and Current values
         If objRow(strColName, DataRowVersion.Current) <> _
            objRow(strColName, DataRowVersion.Original) Then

            'this column's data has been modified in the disconnected DataSet
            'note: this does not detect concurrent changes to the source data

            'have to get format of DateTime exactly right for a comparison
            If objColumn.DataType.ToString() = "System.DateTime" Then
               datRowDateValue = objRow(strColName, DataRowVersion.Original)
               strRowValue = datRowDateValue.ToString("yyyy-MM-dd\ HH:mm:ss")
            Else
               strRowValue = objRow(strColName, DataRowVersion.Original)
            End If

            strSQL += strColName & "='" & objRow(strColName, DataRowVersion.Current) & "', "
            strWhere += " AND " & strColName & "='" & strRowValue & "'"

         End If

      Next

      'strip off extra comma and space from end of string
      strSQL = Left(strSQL, Len(strSQL) - 2) & strWhere
      'and assemble SQL statement
      strResults += "* " & strSQL & " ... "
      objCommand.CommandText = strSQL

      Try
         intRowsAffected = objCommand.ExecuteNonQuery()
         If intRowsAffected > 0 Then
            strResults += "updated <b>" & intRowsAffected & "</b> row(s).<p />"
         Else
            strResults += "<b>Error</b>: Row has been changed by another user<p />"
         End If
      Catch objError As Exception
         'display error details
         strResults += "<b>Error</b>: " & objError.Message & " - " & objError.Source & "<p />"
      End Try

   Next
   outUpdates.InnerHtml = strResults
   objTransaction.Rollback()
   objConnect.Close()

End Sub
</script>


<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
